package session02.orm;

import session02.bean.ChildBean;
import session02.bean.HusbandBean;
import session02.bean.WifeBean;

import java.sql.*;
import java.util.ArrayList;

public class TestHusWife {
    public static void main(String[] args) {
/*        //insert
        //插入丈夫
        HusbandBean hus = new HusbandBean(0, "邓超");
        WifeBean wife = new WifeBean(0, "孙俪");
        hus.setMyWife(wife);//要将妻子放入到丈夫中去
        insert(hus);*/

/*        //查询指定的丈夫
        HusbandBean hus = getHusbandByID(2);
        System.out.println(hus);*/

/*        //找指定的夫妻
        HusbandBean hus = getHusbandWifeByID(1);
        System.out.println(hus);*/

/*        //所有的丈夫
        ArrayList<HusbandBean> allHus = getAllHus();
        for (HusbandBean h : allHus) {
            System.out.println(h);
        }*/

        //所有的夫妻
        ArrayList<HusbandBean> husLst = getAllCouple();
        for (HusbandBean h : husLst) {
            System.out.println(h);
        }

/*        //查找某个父亲以及他所有的孩子
        HusbandBean hus = getFatherAndChildren(2);
        System.out.println(hus);
        for (ChildBean c : hus.getMyChildren()) {
            System.out.println(c);
        }*/

        //查找所有姓王的父亲以及他们的孩子
/*        ArrayList<HusbandBean> hus = getChildren("王");
        for (HusbandBean h : hus) {
            System.out.println(h);
            for (ChildBean c : h.getMyChildren()) {
                System.out.println(c);
            }
            System.out.println("======================");
        }*/
    }


    //插入丈夫
    //多条DML语句，要使用事务
    public static void insert(HusbandBean hus) {
        String sql1 = "insert into t_husband (f_name) values (?)";
        String sql2 = "insert into t_wife(f_name,fk_husid) values (?,?)";
        try (Connection con = DBUtil.getConnection()) {
            con.setAutoCommit(false);

            try (PreparedStatement ps1 = con.prepareStatement(sql1, Statement.RETURN_GENERATED_KEYS);
                 PreparedStatement ps2 = con.prepareStatement(sql2)) {

                ps1.setString(1, hus.getName());
                ps1.executeUpdate();
                ResultSet rs = ps1.getGeneratedKeys();
                int husID = 0;
                while (rs.next()) {
                    husID = rs.getInt(1);
                }

                ps2.setString(1, hus.getMyWife().getName());
                ps2.setInt(2, husID);
                ps2.executeUpdate();

                con.commit();
            } catch (SQLException e) {
                con.rollback();
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //查询指定的丈夫
    public static HusbandBean getHusbandByID(int id) {
        HusbandBean hus = null;

        String sql = "select pk_husid,f_name from t_husband where pk_husid = ?";
        try (Connection con = DBUtil.getConnection();
             PreparedStatement ps = con.prepareStatement(sql)) {
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                hus = new HusbandBean();
                hus.setHusID(rs.getInt("pk_husid"));
                hus.setName(rs.getString("f_name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return hus;
    }

    //找指定的夫妻
    public static HusbandBean getHusbandWifeByID(int wifeid) {
        HusbandBean hus = null;

        String sql = "select pk_husid,t_husband.f_name as husname," +
                "pk_wifeid,t_wife.f_name as wifename " +
                "from t_husband join t_wife on pk_husid=fk_husid " +
                "where pk_wifeid = ?";
        try (Connection con = DBUtil.getConnection();
             PreparedStatement ps = con.prepareStatement(sql)) {
            ps.setInt(1, wifeid);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                hus = new HusbandBean();
                hus.setHusID(rs.getInt("pk_husid"));
                hus.setName(rs.getString("husname"));

                WifeBean wife = new WifeBean();
                wife.setWifeID(rs.getInt("pk_wifeid"));
                wife.setName(rs.getString("wifename"));

                hus.setMyWife(wife);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return hus;
    }

    //所有的丈夫
    public static ArrayList<HusbandBean> getAllHus() {
        ArrayList<HusbandBean> husLst = new ArrayList<>();

        String sql = "select pk_husid,f_name from t_husband";
        try (Connection con = DBUtil.getConnection();
             PreparedStatement ps = con.prepareStatement(sql)) {
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                HusbandBean hus = new HusbandBean();
                hus.setHusID(rs.getInt("pk_husid"));
                hus.setName(rs.getString("f_name"));

                husLst.add(hus);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return husLst;
    }

    //所有的夫妻
    public static ArrayList<HusbandBean> getAllCouple() {
        ArrayList<HusbandBean> husLst = new ArrayList<>();
        String sql = "select pk_husid,h.f_name,w.pk_wifeid,w.f_name from t_husband h,t_wife w where pk_husid=fk_husid";
        try (Connection con = DBUtil.getConnection();
             Statement st = con.createStatement()) {
            ResultSet rs = st.executeQuery(sql);
            while (rs.next()) {
                HusbandBean hus = new HusbandBean();
                hus.setHusID(rs.getInt("pk_husid"));
                hus.setName(rs.getString("h.f_name"));

                WifeBean wife = new WifeBean();
                wife.setWifeID(rs.getInt("w.pk_wifeid"));
                wife.setName(rs.getString("w.f_name"));

                hus.setMyWife(wife);
                husLst.add(hus);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return husLst;
    }

    //查找某个父亲以及他所有的孩子
    public static HusbandBean getFatherAndChildren(int id) {
        HusbandBean hus = null;
        String sql = "select pk_husid,f_name,pk_childID,f_childname from t_husband join t_child on pk_husid = fk_husid WHERE pk_husid = ?";

        try (Connection con = DBUtil.getConnection();
             PreparedStatement ps = con.prepareStatement(sql)) {
            ps.setInt(1, id);

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                if (hus == null) {
                    hus = new HusbandBean();
                    hus.setHusID(rs.getInt("pk_husid"));
                    hus.setName(rs.getString("f_name"));
                }
                ChildBean c = new ChildBean();
                c.setChildID(rs.getInt("pk_childid"));
                c.setName(rs.getString("f_childname"));
                c.setMyDad(hus);

                hus.getMyChildren().add(c);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return hus;
    }

    //查找所有姓王的父亲以及他们的孩子
    public static ArrayList<HusbandBean> getChildren(String name) {
        ArrayList<HusbandBean> fatherLst = new ArrayList<>();
        String sql = "select pk_husid,f_name,pk_childID,f_childname FROM t_husband,t_child WHERE pk_husid=fk_husid and f_name like ? order by pk_husid";
        try (Connection con = DBUtil.getConnection();
             PreparedStatement ps = con.prepareStatement(sql)) {
            ps.setString(1, name + "%");
            ResultSet rs = ps.executeQuery();

            HusbandBean father = null;
            while (rs.next()) {
                int fatherID = rs.getInt("pk_husid");//当前id
                if (father == null || father.getHusID() != fatherID) {
                    father = new HusbandBean();
                    father.setHusID(rs.getInt("pk_husid"));
                    father.setName(rs.getString("f_name"));
                    fatherLst.add(father);
                }
                ChildBean childBean = new ChildBean();
                childBean.setChildID(rs.getInt("pk_childID"));
                childBean.setName(rs.getString("f_childname"));
                father.getMyChildren().add(childBean);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return fatherLst;
    }
}
